CREATE TABLE contract_payments
(
  id numeric NOT NULL,
  contract_id_fk numeric,
  personal_fk numeric,
  amount numeric(15.2),
  date_trans timestamp(6) without time zone,
  partener_fk numeric,
  comments character varying(500),
  CONSTRAINT contract_payments_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE contract_payments
  OWNER TO mf;
  
  CREATE TABLE client_files
(
  id numeric NOT NULL,
  reg_persons_id_fk numeric,
  reg_companies_id_fk numeric,
  file_id numeric,
  ref_domains_fk numeric,
  CONSTRAINT client_files_pk PRIMARY KEY (id),
  CONSTRAINT reg_companies_id_fk FOREIGN KEY (reg_companies_id_fk)
      REFERENCES reg_companies (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT reg_persons_id_fk FOREIGN KEY (reg_persons_id_fk)
      REFERENCES reg_persons (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE client_files
  OWNER TO mf;
  
  
  insert into client_files

select af.id
,(select apf.reg_persons_fk from application_pers_fiz apf where apf.id=c.application_pers_fiz_fk) regPers
,(select apj.reg_companies_fk from application_pers_jur apj where apj.id=c.application_pers_jur_fk) regJur
, af.file_id
, c.ref_domains_fk
from application_files af, contracts c	
where af.contract_id_fk=c.id;



CREATE OR REPLACE FUNCTION "public"."get_reg_parteners_cname_by_id" (partnerid numeric)  RETURNS text
  VOLATILE
AS $dbvis$
DECLARE
   result text;
 BEGIN
     select company_name into result from reg_partners where id = partnerid;
  RETURN result;
END;
$dbvis$ LANGUAGE plpgsql

ALTER TABLE
    magfin.public.contract_payments ALTER COLUMN amount TYPE NUMERIC  USING amount::numeric;
